package main

import (
	"fmt"
	"log"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"gorm.io/gorm/schema"
)

type CreditCard struct {
	gorm.Model
	Number string
	UserID uint
}

type User struct {
	gorm.Model
	Name       string
	Age        uint8
	Birthday   time.Time
	CreditCard CreditCard
}

type Result struct {
	Date  time.Time
	Total int
}

func selectFirst(db *gorm.DB) {
	var user User
	result := db.First(&user)
	if result.Error != nil {
		log.Fatalf("failed select with first: %v\n", result.Error)
	}
	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectFirstWithQuery(db *gorm.DB) {
	var user User
	// result := db.First(&user, 2)
	// result := db.First(&user, "2")
	result := db.Find(&user, []int{1, 2, 3})
	if result.Error != nil {
		log.Fatalf("failed select with first: %v\n", result.Error)
	}
	if result.RowsAffected == 0 {
		log.Fatalf("record not found: %v\n", result.RowsAffected)
	}
	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectTake(db *gorm.DB) {
	var user User
	result := db.Take(&user)
	if result.Error != nil {
		log.Fatalf("failed select with take: %v\n", result.Error)
	}
	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectLast(db *gorm.DB) {
	var user User
	result := db.Last(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithMap1(db *gorm.DB) {
	user := map[string]interface{}{}
	result := db.Model(&User{}).First(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user["id"],
		user["name"],
		user["age"],
		user["birthday"],
		user["credit_card"],
		result.RowsAffected,
	)
}

func selectWithMap2(db *gorm.DB) {
	user := map[string]interface{}{}
	result := db.Table("gorm-user").First(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user["id"],
		user["name"],
		user["age"],
		user["birthday"],
		user["credit_card"],
		result.RowsAffected,
	)
}

func selectWithMap3(db *gorm.DB) {
	user := map[string]interface{}{}
	result := db.Table("gorm-user").Take(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user["id"],
		user["name"],
		user["age"],
		user["birthday"],
		user["credit_card"],
		result.RowsAffected,
	)
}

func selectWithFind(db *gorm.DB) {
	var users []User
	result := db.Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with find: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithWhereEqual(db *gorm.DB) {
	var user User
	result := db.Where("name = ?", "Kobe").First(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithWhereNotEqual(db *gorm.DB) {
	var users []User
	result := db.Where("name <> ?", "leo").Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with find: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithWhereIN(db *gorm.DB) {
	var users []User
	result := db.Where("name IN ?", []string{"leo", "Messi", "Kobe"}).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with find: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithWhereLike(db *gorm.DB) {
	var users []User
	result := db.Where("name like ?", "%ssi%").Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with find: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithWhereAnd(db *gorm.DB) {
	var users []User
	result := db.Where("name like ? and age >=? ", "%ssi%", 20).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with find: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithStructField(db *gorm.DB) {
	var user User
	result := db.Where(&User{Name: "Messi"}).Find(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithInline1(db *gorm.DB) {
	var user User
	result := db.First(&user, "id = ?", 10)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithInline2(db *gorm.DB) {
	var user User
	result := db.Find(&user, "name = ?", "Messi")
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithInline3(db *gorm.DB) {
	var users []User
	result := db.Find(&users, "name <> ? AND age > ?", "Messi", 30)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithInline4(db *gorm.DB) {
	var user User
	result := db.Find(&user, User{Name: "Messi"})
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithInline5(db *gorm.DB) {
	var user User
	result := db.Find(&user, map[string]interface{}{"age": 32})
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithNot1(db *gorm.DB) {
	var user User
	result := db.Not("name = ?", "Messi").First(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithNot2(db *gorm.DB) {
	var users []User
	result := db.Not(map[string]interface{}{"name": []string{"Jack", "Jack_1"}}).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithNot3(db *gorm.DB) {
	var user User
	result := db.Not(User{Name: "Kobe"}).First(&user)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
		user.ID,
		user.Name,
		user.Age,
		user.Birthday,
		user.CreditCard.Number,
		result.RowsAffected,
	)
}

func selectWithNot4(db *gorm.DB) {
	var users []User
	result := db.Not([]uint{1, 2, 3}).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithOr1(db *gorm.DB) {
	var users []User
	result := db.Where("name like ?", "%ssi").Or("age > ?", 20).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithOr2(db *gorm.DB) {
	var users []User
	result := db.Where("name like ?", "%ssi%").Or(User{Age: 32}).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithOr3(db *gorm.DB) {
	var users []User
	result := db.Where("name like ?", "%ssi%").Or(map[string]interface{}{"age": 18}).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithFields1(db *gorm.DB) {
	var users []User
	result := db.Select("name", "age").Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithFields2(db *gorm.DB) {
	var users []User
	result := db.Select([]string{"id", "age"}).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithFields3(db *gorm.DB) {
	rows, err := db.Table("gorm-user").Select("COALESCE(age, ?)", 32).Rows()

	if err != nil {
		log.Fatalf("failed select with last: %v\n", err)
	}

	defer rows.Close()

	for rows.Next() {
		var user User
		if err := rows.Scan(&user.Age); err != nil {
			log.Fatalf("failed to scan row: %v\n", err)
		}
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
		)
	}
}

func selectWithOrder1(db *gorm.DB) {
	var users []User
	result := db.Order("age asc").Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithOrder2(db *gorm.DB) {
	var users []User
	result := db.Order("age asc").Order("name desc").Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithLimitOrOffset1(db *gorm.DB) {
	var users []User
	result := db.Limit(3).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}

	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithLimitOrOffset2(db *gorm.DB) {
	var users1 []User
	var users2 []User
	result := db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	for _, user := range users1 {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
	fmt.Println()
	for _, user := range users2 {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithLimitOrOffset3(db *gorm.DB) {
	var users []User
	result := db.Offset(3).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithLimitOrOffset4(db *gorm.DB) {
	var users []User
	result := db.Limit(5).Offset(0).Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithLimitOrOffset5(db *gorm.DB) {
	var users1 []User
	var users2 []User
	result := db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	for _, user := range users1 {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
	fmt.Println()
	for _, user := range users2 {
		fmt.Printf("id: %v, name: %v, age: %v, birthday: %v, number: %v, affected: %d\n",
			user.ID,
			user.Name,
			user.Age,
			user.Birthday,
			user.CreditCard.Number,
			result.RowsAffected,
		)
	}
}

func selectWithGroup1(db *gorm.DB) {
	var result Result
	res := db.Model(&User{}).Select("name, sum(age) as total").Where("name like ?", "%ssi%").Group("name").Find(&result)
	if res.Error != nil {
		log.Fatalf("failed select with last: %v\n", res.Error)
	}
	fmt.Printf("date: %v, total: %d\n",
		result.Date,
		result.Total,
	)
}

func selectWithDistinct(db *gorm.DB) {
	var users []User
	result := db.Distinct("name", "age").Order("name, age desc").Find(&users)
	if result.Error != nil {
		log.Fatalf("failed select with last: %v\n", result.Error)
	}
	for _, user := range users {
		fmt.Printf("name: %v, age: %v\n",
			user.Name,
			user.Age,
		)
	}
}
func main() {
	dsn := "host=localhost port=5432 user=postgres password=123456 dbname=my-gorm-app sslmode=disable TimeZone=Asia/ShangHai"
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			SingularTable: true,
			TablePrefix:   "gorm-",
		},
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatalf("failed connect to database: %v\n", err)
	}
	// selectFirst(db)
	// selectFirstWithQuery(db)
	// selectTake(db)
	// selectLast(db)
	// selectWithMap1(db)
	// selectWithMap2(db)
	// selectWithMap3(db)
	// selectWithFind(db)
	// selectWithWhereEqual(db)
	// selectWithWhereNotEqual(db)
	// selectWithWhereIN(db)
	// selectWithWhereLike(db)
	// selectWithWhereAnd(db)
	// selectWithStructField(db)
	// selectWithInline1(db)
	// selectWithInline2(db)
	// selectWithInline3(db)
	// selectWithInline4(db)
	// selectWithInline5(db)
	// selectWithNot1(db)
	// selectWithNot2(db)
	// selectWithNot3(db)
	// selectWithNot4(db)
	// selectWithOr1(db)
	// selectWithOr2(db)
	// selectWithOr3(db)
	// selectWithFields1(db)
	// selectWithFields2(db)
	// selectWithFields3(db)
	// selectWithOrder1(db)
	// selectWithOrder2(db)
	// selectWithLimitOrOffset1(db)
	// selectWithLimitOrOffset2(db)
	// selectWithLimitOrOffset3(db)
	// selectWithLimitOrOffset4(db)
	// selectWithLimitOrOffset5(db)
	// selectWithGroup1(db)
	selectWithDistinct(db)
}
